import json
from sql import SQLite

"""
"city_code": "101010100",
"name": "北京",
"province_name": "北京",
"parent_name": "中国",
"lng": "39.904989",
"lat": "116.405285"
"""

st = SQLite("study.db")
st.connect_db()
create_db = '''
CREATE TABLE city (
    id            [UNSIGNED INT AUTO_INCREMENT] (9) PRIMARY KEY
                                                    NOT NULL
                                                    UNIQUE,
    city_name     CHAR (20)                         NOT NULL
);
'''
st.create_table(create_db)

data = []
with open("city_id.txt", 'r', encoding='UTF-8') as f:

    # '"北京"=>"101010100",
    lines = f.readlines()
    for line in lines:
        data_item = []
        city_name = line.split("=>")[0][1:-1]
        city_id = line.split("=>")[-1][1:-3]
        data_item.append(city_id)
        data_item.append(city_name)
        data.append(data_item)

table_name = "city"
field = "(id, city_name)"
precompile = st.precompile(field)

sql_cmd = f"INSERT INTO {table_name} {field} VALUES {precompile}"
st.insert_data(sql_cmd, data, batch=True)
